﻿using  System;
using  System.Diagnostics;
using  System.Web.Security;
using  System.Web;
using  System.Web.Configuration;
using  System.Security.Principal;
using  System.Security.Permissions;
using  System.Globalization;
using  System.Runtime.Serialization;
using  System.Collections;
using  System.Collections.Specialized;
using  System.Data;
using  System.Data.SqlClient;
using  System.Data.SqlTypes;
using  System.Text;
using  System.Configuration.Provider;
using  System.Configuration;
using  System.Web.DataAccess;
using  System.Web.Hosting;
using  System.Web.Util;

using  AddonNice;
using  AddonNice.Security;
using  AddonNice.Core;
using  AddonNice.Configuration;
using  AddonNice.Settings;
using  AddonNice.Helpers;
using  AddonNice.CRMWrapper;

namespace AddonNice.Authentication
{



    // Remove CAS from sample: [AspNetHostingPermission(SecurityAction.LinkDemand, Level=AspNetHostingPermissionLevel.Minimal)]
    // Remove CAS from sample: [AspNetHostingPermission(SecurityAction.InheritanceDemand, Level=AspNetHostingPermissionLevel.Minimal)]
    public class AddonNiceSqlRoleProvider : RoleProvider
    {
        private int     _SchemaVersionCheck;
        private string  _sqlConnectionString;
        private int     _CommandTimeout;

        #region Public properties

        private int CommandTimeout
        {
            get{ return _CommandTimeout; }
        }

        #endregion Public properties

        public override  void Initialize(string name, NameValueCollection config)
        {
			if ( SecurityTraceSwitch.Sw.TraceVerbose )
                Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]AddonNiceSqlRoleProvider Initialize BEG name: {0}",name),SecurityTraceSwitch.Sw.Info);
            // Remove CAS from sample: HttpRuntime.CheckAspNetHostingPermission (AspNetHostingPermissionLevel.Low, SR.Feature_not_supported_at_this_level);
            if (config == null)
               throw new ArgumentNullException("config");

            if (String.IsNullOrEmpty(name))
                name    =   "AddonNiceSqlRoleProvider";
            if (string.IsNullOrEmpty(config["description"])) 
            {
                config.Remove("description");
                config.Add("description", SR.GetString(SR.RoleCRMProvider_description));
            }
            base.Initialize(name, config);

            _SchemaVersionCheck     =   0;

            _CommandTimeout         =   SecUtility.GetIntValue( config, "commandTimeout", 30, true, 0 );

            string temp = config["connectionStringName"];
            if (temp == null || temp.Length < 1)
                throw new ProviderException(SR.GetString(SR.Connection_name_not_specified));
            _sqlConnectionString = SqlConnectionHelper.GetConnectionString(temp, true, true);
            if (_sqlConnectionString == null || _sqlConnectionString.Length < 1) 
            {
                throw new ProviderException(SR.GetString(SR.Connection_string_not_found, temp));
            }

            _DefAppName             =   config["applicationName"];
            if (string.IsNullOrEmpty(_DefAppName))
                _DefAppName         =   SecUtility.GetDefaultAppName();

            if( _DefAppName.Length > 256 )
            {
                throw new ProviderException(SR.GetString(SR.Provider_application_name_too_long));
            }

            config.Remove("connectionStringName");
            config.Remove("applicationName");
            config.Remove("commandTimeout");
   			if ( SecurityTraceSwitch.Sw.TraceVerbose )
                Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]AddonNiceSqlRoleProvider Initialize config.Count: {0}, ApplicationName: '{1}'",config.Count,_DefAppName ),SecurityTraceSwitch.Sw.Info);
            if (config.Count > 0)
            {
                string attribUnrecognized =     config.GetKey(0);
                if (!String.IsNullOrEmpty(attribUnrecognized))
                    throw new ProviderException(SR.GetString(SR.Provider_unrecognized_attribute, attribUnrecognized));
            }
			if ( SecurityTraceSwitch.Sw.TraceVerbose )
                Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]AddonNiceSqlRoleProvider Initialize END name: {0}",name),SecurityTraceSwitch.Sw.Info);
        }

        private void CheckSchemaVersion( SqlConnection connection )
        {
            string[] features =     { "Role Manager" };
            string   version  =     "1";

            SecUtility.CheckSchemaVersion( this,
                                           connection,
                                           features,
                                           version,
                                           ref _SchemaVersionCheck );
        }

        public override bool IsUserInRole(string username, string roleName)
        {
   			if ( SecurityTraceSwitch.Sw.TraceVerbose )
                Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]AddonNiceSqlRoleProvider IsUserInRole BEG username: {0}, roleName: '{1}'",username,roleName ),SecurityTraceSwitch.Sw.Info);
            SecUtility.CheckParameter(ref roleName, true, true, true, 256, "roleName");
            SecUtility.CheckParameter(ref username, true, false, true, 256, "username");
            if (username.Length < 1)
                return false;

            try 
            {
                SqlConnectionHolder holder  =   null;
                try 
                {
                    holder                  =   SqlConnectionHelper.GetConnection(_sqlConnectionString, true);
                    CheckSchemaVersion( holder.Connection );

                    SqlCommand    cmd       =   new SqlCommand("dbo.AddonNice_UsersInRoles_IsUserInRole", holder.Connection);
                    cmd.CommandType         =   CommandType.StoredProcedure;
                    cmd.CommandTimeout      =   CommandTimeout;

                    SqlParameter p          =   new SqlParameter("@ReturnValue", SqlDbType.Int);
                    p.Direction             =   ParameterDirection.ReturnValue;
                    cmd.Parameters.Add(p);
                    cmd.Parameters.Add(CreateInputParam("@ApplicationName", SqlDbType.NVarChar, ApplicationName));
                    cmd.Parameters.Add(CreateInputParam("@UserName", SqlDbType.NVarChar, username));
                    cmd.Parameters.Add(CreateInputParam("@RoleName", SqlDbType.NVarChar, roleName));
                    cmd.ExecuteNonQuery();
                    int iStatus             =   GetReturnValue(cmd);

                    switch(iStatus)
                    {
                    case 0:
                        return false;
                    case 1:
                        return true;
                    case 2:
                        return false;
                        // throw new ProviderException(SR.GetString(SR.Provider_user_not_found));
                    case 3:
                        return false; // throw new ProviderException(SR.GetString(SR.Provider_role_not_found, roleName));
                    }
                    throw new ProviderException(SR.GetString(SR.Provider_unknown_failure));
                }
                finally
                {
                    if( holder != null )
                    {
                        holder.Close();
                        holder              =   null;
                    }
                }
            }
            catch
            {
                throw;
            }
        }

        public override  string [] GetRolesForUser(string username)
        {
   			if ( SecurityTraceSwitch.Sw.TraceVerbose )
                Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]AddonNiceSqlRoleProvider GetRolesForUser BEG username: {0}",username),SecurityTraceSwitch.Sw.Info);
            SecUtility.CheckParameter(ref username, true, false, true, 256, "username");
            if (username.Length < 1)
                return new string[0];
            try 
            {
                SqlConnectionHolder holder          =   null;

                try 
                {
                    holder                          =   SqlConnectionHelper.GetConnection(_sqlConnectionString, true);
                    CheckSchemaVersion( holder.Connection );

                    SqlCommand      cmd             =   new SqlCommand("dbo.AddonNice_UsersInRoles_GetRolesForUser", holder.Connection);
                    SqlParameter    p               =   new SqlParameter("@ReturnValue", SqlDbType.Int);
                    SqlDataReader   reader          =   null;
                    StringCollection       sc       =   new StringCollection();

                    cmd.CommandType                 =   CommandType.StoredProcedure;
                    cmd.CommandTimeout              =   CommandTimeout;

                    p.Direction                     =   ParameterDirection.ReturnValue;
                    cmd.Parameters.Add(p);
                    cmd.Parameters.Add(CreateInputParam("@ApplicationName", SqlDbType.NVarChar, ApplicationName));
                    cmd.Parameters.Add(CreateInputParam("@UserName", SqlDbType.NVarChar, username));
                    try 
                    {
                        reader                      =   cmd.ExecuteReader(CommandBehavior.SequentialAccess);
                        while (reader.Read())
                            sc.Add(reader.GetString(0));
                    }
                    catch
                    {
                        throw;
                    }
                    finally
                    {
                        if (reader != null)
                            reader.Close();
                    }
		            if ( SecurityTraceSwitch.Sw.TraceVerbose )
                        Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]AddonNiceSqlRoleProvider GetRolesForUser END count: {0}",sc.Count),SecurityTraceSwitch.Sw.Info);
                    if (sc.Count > 0)
                    {
                        String [] strReturn         =   new String[sc.Count];
                        sc.CopyTo(strReturn, 0);
                        return strReturn;
                    }

                    switch(GetReturnValue(cmd))
                    {
                        case 0:
                            return new string[0];
                        case 1:
                            return new string[0];
                            //throw new ProviderException(SR.GetString(SR.Provider_user_not_found));
                        default:
                            throw new ProviderException(SR.GetString(SR.Provider_unknown_failure));
                    }
                }
                finally
                {
                    if( holder != null )
                    {
                        holder.Close();
                        holder = null;
                    }
                }
            }
            catch
            {
                throw;
            }
        }

        public override  void CreateRole(string roleName)
        {
   			if ( SecurityTraceSwitch.Sw.TraceVerbose )
                Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]AddonNiceSqlRoleProvider CreateRole BEG roleName: {0}",roleName),SecurityTraceSwitch.Sw.Info);
            SecUtility.CheckParameter(ref roleName, true, true, true, 256, "roleName");
            try 
            {
                SqlConnectionHolder holder  =   null;

                try 
                {
                    holder                  =   SqlConnectionHelper.GetConnection(_sqlConnectionString, true);
                    CheckSchemaVersion(holder.Connection);
                    SqlCommand cmd          =   new SqlCommand("dbo.AddonNice_Roles_CreateRole", holder.Connection);
                    cmd.CommandType         =   CommandType.StoredProcedure;
                    cmd.CommandTimeout      =   CommandTimeout;

                    SqlParameter p          =   new SqlParameter("@ReturnValue", SqlDbType.Int);

                    p.Direction             =   ParameterDirection.ReturnValue;
                    cmd.Parameters.Add(p);
                    cmd.Parameters.Add(CreateInputParam("@ApplicationName", SqlDbType.NVarChar, ApplicationName));
                    cmd.Parameters.Add(CreateInputParam("@RoleName", SqlDbType.NVarChar, roleName));
                    cmd.ExecuteNonQuery();

                    int returnValue         =   GetReturnValue(cmd);

                    switch (returnValue) 
                    {
                        case 0 :
                            return;
                        case 1 :
                            throw new ProviderException(SR.GetString(SR.Provider_role_already_exists, roleName));
                        default :
                            throw new ProviderException(SR.GetString(SR.Provider_unknown_failure));
                    }
                }
                finally
                {
                    if( holder != null )
                    {
                        holder.Close();
                        holder = null;
                    }
                }
            }
            catch
            {
                throw;
            }
        }

        public override bool DeleteRole(string roleName, bool throwOnPopulatedRole)
        {
   			if ( SecurityTraceSwitch.Sw.TraceVerbose )
                Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]AddonNiceSqlRoleProvider DeleteRole BEG roleName: {0}, throwOnPopulatedRole: {1}",roleName,throwOnPopulatedRole),SecurityTraceSwitch.Sw.Info);
            SecUtility.CheckParameter(ref roleName, true, true, true, 256, "roleName");
            try 
            {
                SqlConnectionHolder holder      =   null;

                try 
                {
                    holder                      =   SqlConnectionHelper.GetConnection(_sqlConnectionString, true);
                    CheckSchemaVersion( holder.Connection );

                    SqlCommand    cmd           =   new SqlCommand("dbo.AddonNice_Roles_DeleteRole", holder.Connection);

                    cmd.CommandType             =   CommandType.StoredProcedure;
                    cmd.CommandTimeout          =   CommandTimeout;

                    SqlParameter p              =   new SqlParameter("@ReturnValue", SqlDbType.Int);
                    p.Direction                 =   ParameterDirection.ReturnValue;
                    cmd.Parameters.Add(p);
                    cmd.Parameters.Add(CreateInputParam("@ApplicationName", SqlDbType.NVarChar, ApplicationName));
                    cmd.Parameters.Add(CreateInputParam("@RoleName", SqlDbType.NVarChar, roleName));
                    cmd.Parameters.Add(CreateInputParam("@DeleteOnlyIfRoleIsEmpty", SqlDbType.Bit, throwOnPopulatedRole ? 1 : 0));
                    cmd.ExecuteNonQuery();
                    int returnValue = GetReturnValue(cmd);

                    if( returnValue == 2 )
                    {
                        throw new ProviderException(SR.GetString(SR.Role_is_not_empty));
                    }

                    return ( returnValue == 0 );
                }
                finally
                {
                    if( holder != null )
                    {
                        holder.Close();
                        holder = null;
                    }
                }
            }
            catch
            {
                throw;
            }
        }

        public override  bool RoleExists(string roleName)
        {
   			if ( SecurityTraceSwitch.Sw.TraceVerbose )
                Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]AddonNiceSqlRoleProvider RoleExists BEG roleName: {0}",roleName),SecurityTraceSwitch.Sw.Info);
            SecUtility.CheckParameter( ref roleName, true, true, true, 256, "roleName" );

            try 
            {
                SqlConnectionHolder holder  =   null;

                try 
                {
                    holder                  =   SqlConnectionHelper.GetConnection(_sqlConnectionString, true);
                    CheckSchemaVersion( holder.Connection );

                    SqlCommand    cmd       =   new SqlCommand("dbo.AddonNice_Roles_RoleExists", holder.Connection);

                    cmd.CommandType         =   CommandType.StoredProcedure;
                    cmd.CommandTimeout      =   CommandTimeout;

                    SqlParameter p          =   new SqlParameter("@ReturnValue", SqlDbType.Int);
                    p.Direction             =   ParameterDirection.ReturnValue;
                    cmd.Parameters.Add(p);
                    cmd.Parameters.Add(CreateInputParam("@ApplicationName", SqlDbType.NVarChar, ApplicationName));
                    cmd.Parameters.Add(CreateInputParam("@RoleName", SqlDbType.NVarChar, roleName));
                    cmd.ExecuteNonQuery();
                    int returnValue         =   GetReturnValue(cmd);

                    switch(returnValue)
                    {
                        case 0:
                            return false;
                        case 1:
                            return true;
                    }
                    throw new ProviderException(SR.GetString(SR.Provider_unknown_failure));
                }
                finally
                {
                    if( holder != null )
                    {
                        holder.Close();
                        holder = null;
                    }
                }
            }
            catch
            {
                throw;
            }
        }

        public override void AddUsersToRoles(string[] usernames, string[] roleNames)
        {
   			if ( SecurityTraceSwitch.Sw.TraceVerbose )
                Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]AddonNiceSqlRoleProvider AddUsersToRoles BEG usernames.length: {0}, roleNames.Length: {1}",usernames.Length,roleNames.Length),SecurityTraceSwitch.Sw.Info);
            SecUtility.CheckArrayParameter(ref roleNames, true, true, true, 256, "roleNames");
            SecUtility.CheckArrayParameter(ref usernames, true, true, true, 256, "usernames");

            bool beginTranCalled                =   false;
            try 
            {
                SqlConnectionHolder holder      =   null;
                try
                {
                    holder                      =   SqlConnectionHelper.GetConnection(_sqlConnectionString, true);
                    CheckSchemaVersion(holder.Connection);
                    int numUsersRemaing         =   usernames.Length;
                    while (numUsersRemaing > 0)
                    {
                        int iter;
                        string allUsers         =   usernames[usernames.Length - numUsersRemaing];
                        numUsersRemaing--;
                        for (iter = usernames.Length - numUsersRemaing; iter < usernames.Length; iter++)
                        {
                            if (allUsers.Length + usernames[iter].Length + 1 >= 4000)
                                break;
                            allUsers            +=  "," + usernames[iter];
                            numUsersRemaing--;
                        }

                        int numRolesRemaining   =   roleNames.Length;
                        while (numRolesRemaining > 0)
                        {
                            string allRoles     =   roleNames[roleNames.Length - numRolesRemaining];
                            numRolesRemaining--;
                            for (iter = roleNames.Length - numRolesRemaining; iter < roleNames.Length; iter++)
                            {
                                if (allRoles.Length + roleNames[iter].Length + 1 >= 4000)
                                    break;
                                allRoles        +=  "," + roleNames[iter];
                                numRolesRemaining--;
                            }
                            //
                            // Note:  ADO.NET 2.0 introduced the TransactionScope class - in your own code you should use TransactionScope
                            //            rather than explicitly managing transactions with the TSQL BEGIN/COMMIT/ROLLBACK statements.
                            //
                            if (!beginTranCalled && (numUsersRemaing > 0 || numRolesRemaining > 0)) {
                                (new SqlCommand("BEGIN TRANSACTION", holder.Connection)).ExecuteNonQuery();
                                beginTranCalled = true;
                            }
                            AddUsersToRolesCore(holder.Connection, allUsers, allRoles);
                        }
                    }
                    if (beginTranCalled) 
                    {
                        (new SqlCommand("COMMIT TRANSACTION", holder.Connection)).ExecuteNonQuery();
                        beginTranCalled = false;
                    }
                } 
                catch  
                {
                    if (beginTranCalled) 
                    {
                        try 
                        {
                            (new SqlCommand("ROLLBACK TRANSACTION", holder.Connection)).ExecuteNonQuery();
                        } catch {
                        }
                        beginTranCalled = false;
                    }
                    throw;
                } 
                finally 
                {
                    if( holder != null )
                    {
                        holder.Close();
                        holder = null;
                    }
                }
            } 
            catch 
            {
                throw;
            }
        }

        private void  AddUsersToRolesCore(SqlConnection conn, string usernames, string roleNames)
        {
   			if ( SecurityTraceSwitch.Sw.TraceVerbose )
                Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]AddonNiceSqlRoleProvider AddUsersToRolesCore BEG usernames: {0}, roleNames: {1}",usernames,roleNames),SecurityTraceSwitch.Sw.Info);
            SqlCommand      cmd     =   new SqlCommand("dbo.AddonNice_UsersInRoles_AddUsersToRoles", conn);
            SqlDataReader   reader  =   null;
            SqlParameter    p       =   new SqlParameter("@ReturnValue", SqlDbType.Int);
            string          s1      =   String.Empty, s2 = String.Empty;

            cmd.CommandType         =   CommandType.StoredProcedure;
            cmd.CommandTimeout      =   CommandTimeout;

            p.Direction             =   ParameterDirection.ReturnValue;
            cmd.Parameters.Add(p);
            cmd.Parameters.Add(CreateInputParam("@ApplicationName", SqlDbType.NVarChar, ApplicationName));
            cmd.Parameters.Add(CreateInputParam("@RoleNames", SqlDbType.NVarChar, roleNames));
            cmd.Parameters.Add(CreateInputParam("@UserNames", SqlDbType.NVarChar, usernames));
            cmd.Parameters.Add(CreateInputParam("@CurrentTimeUtc", SqlDbType.DateTime, DateTime.UtcNow));
            try {
                reader              =   cmd.ExecuteReader(CommandBehavior.SingleRow);
                if (reader.Read()) 
                {
                    if (reader.FieldCount > 0)
                        s1 = reader.GetString(0);
                    if (reader.FieldCount > 1)
                        s2 = reader.GetString(1);
                }
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }
            switch(GetReturnValue(cmd))
            {
            case 0:
                return;
            case 1:
                throw new ProviderException(SR.GetString(SR.Provider_this_user_not_found, s1));
            case 2:
                throw new ProviderException(SR.GetString(SR.Provider_role_not_found, s1));
            case 3:
                throw new ProviderException(SR.GetString(SR.Provider_this_user_already_in_role, s1, s2));
            }
            throw new ProviderException(SR.GetString(SR.Provider_unknown_failure));
        }

        public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
        {
   			if ( SecurityTraceSwitch.Sw.TraceVerbose )
                Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]AddonNiceSqlRoleProvider RemoveUsersFromRoles BEG usernames.length: {0}, roleNames.Length: {1}",usernames.Length,roleNames.Length),SecurityTraceSwitch.Sw.Info);
            SecUtility.CheckArrayParameter(ref roleNames, true, true, true, 256, "roleNames");
            SecUtility.CheckArrayParameter(ref usernames, true, true, true, 256, "usernames");

            bool beginTranCalled            =   false;
            try 
            {
                SqlConnectionHolder holder  =   null;
                try
                {
                    holder                  =   SqlConnectionHelper.GetConnection(_sqlConnectionString, true);
                    CheckSchemaVersion( holder.Connection );
                    int numUsersRemaing     =   usernames.Length;
                    while (numUsersRemaing > 0) 
                    {
                        int iter;
                        string allUsers = usernames[usernames.Length - numUsersRemaing];
                        numUsersRemaing--;
                        for (iter = usernames.Length - numUsersRemaing; iter < usernames.Length; iter++) 
                        {
                            if (allUsers.Length + usernames[iter].Length + 1 >= 4000)
                                break;
                            allUsers += "," + usernames[iter];
                            numUsersRemaing--;
                        }

                        int numRolesRemaining = roleNames.Length;
                        while (numRolesRemaining > 0) 
                        {
                            string allRoles = roleNames[roleNames.Length - numRolesRemaining];
                            numRolesRemaining--;
                            for (iter = roleNames.Length - numRolesRemaining; iter < roleNames.Length; iter++) 
                            {
                                if (allRoles.Length + roleNames[iter].Length + 1 >= 4000)
                                    break;
                                allRoles += "," + roleNames[iter];
                                numRolesRemaining--;
                            }
                            //
                            // Note:  ADO.NET 2.0 introduced the TransactionScope class - in your own code you should use TransactionScope
                            //            rather than explicitly managing transactions with the TSQL BEGIN/COMMIT/ROLLBACK statements.
                            //
                            if (!beginTranCalled && (numUsersRemaing > 0 || numRolesRemaining > 0)) 
                            {
                                (new SqlCommand("BEGIN TRANSACTION", holder.Connection)).ExecuteNonQuery();
                                beginTranCalled = true;
                            }
                            RemoveUsersFromRolesCore(holder.Connection, allUsers, allRoles);
                        }
                    }
                    if (beginTranCalled) 
                    {
                        (new SqlCommand("COMMIT TRANSACTION", holder.Connection)).ExecuteNonQuery();
                        beginTranCalled = false;
                    }
                } 
                catch  
                {
                    if (beginTranCalled) 
                    {
                        (new SqlCommand("ROLLBACK TRANSACTION", holder.Connection)).ExecuteNonQuery();
                        beginTranCalled = false;
                    }
                    throw;
                } 
                finally 
                {
                    if( holder != null )
                    {
                        holder.Close();
                        holder = null;
                    }
                }
            } 
            catch 
            {
                throw;
            }
        }

        private void RemoveUsersFromRolesCore(SqlConnection conn, string usernames, string roleNames)
        {
   			if ( SecurityTraceSwitch.Sw.TraceVerbose )
                Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]AddonNiceSqlRoleProvider RemoveUsersFromRolesCore BEG usernames: {0}, roleNames: {1}",usernames,roleNames),SecurityTraceSwitch.Sw.Info);
            SqlCommand      cmd     = new SqlCommand("dbo.AddonNice_UsersInRoles_RemoveUsersFromRoles", conn);
            SqlDataReader   reader  = null;
            SqlParameter    p       = new SqlParameter("@ReturnValue", SqlDbType.Int);
            string          s1      = String.Empty, s2 = String.Empty;

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandTimeout = CommandTimeout;

            p.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(p);
            cmd.Parameters.Add(CreateInputParam("@ApplicationName", SqlDbType.NVarChar, ApplicationName));
            cmd.Parameters.Add(CreateInputParam("@UserNames", SqlDbType.NVarChar, usernames));
            cmd.Parameters.Add(CreateInputParam("@RoleNames", SqlDbType.NVarChar, roleNames));
            try 
            {
                reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
                if (reader.Read()) 
                {
                    if (reader.FieldCount > 0)
                        s1 = reader.GetString(0);
                    if (reader.FieldCount > 1)
                        s2 = reader.GetString(1);
                }
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }
            switch (GetReturnValue(cmd))
            {
                case 0:
                    return;
                case 1:
                    throw new ProviderException(SR.GetString(SR.Provider_this_user_not_found, s1));
                case 2:
                    throw new ProviderException(SR.GetString(SR.Provider_role_not_found, s2));
                case 3:
                    throw new ProviderException(SR.GetString(SR.Provider_this_user_already_not_in_role, s1, s2));
            }
            throw new ProviderException(SR.GetString(SR.Provider_unknown_failure));
        }

        public override  string [] GetUsersInRole(string roleName)
        {
   			if ( SecurityTraceSwitch.Sw.TraceVerbose )
                Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]AddonNiceSqlRoleProvider GetUsersInRole BEG roleName: {0}",roleName),SecurityTraceSwitch.Sw.Info);
            SecUtility.CheckParameter(ref roleName, true, true, true, 256, "roleName");

            try 
            {
                SqlConnectionHolder holder      =   null;
                try 
                {
                    holder = SqlConnectionHelper.GetConnection(_sqlConnectionString, true);
                    CheckSchemaVersion( holder.Connection );

                    SqlCommand      cmd         =   new SqlCommand("dbo.AddonNice_UsersInRoles_GetUsersInRoles", holder.Connection);
                    SqlDataReader   reader      =   null;
                    SqlParameter    p           =   new SqlParameter("@ReturnValue", SqlDbType.Int);
                    StringCollection    sc      =   new StringCollection();

                    cmd.CommandType             =   CommandType.StoredProcedure;
                    cmd.CommandTimeout          =   CommandTimeout;

                    p.Direction                 =   ParameterDirection.ReturnValue;
                    cmd.Parameters.Add(p);
                    cmd.Parameters.Add(CreateInputParam("@ApplicationName", SqlDbType.NVarChar, ApplicationName));
                    cmd.Parameters.Add(CreateInputParam("@RoleName", SqlDbType.NVarChar, roleName));
                    try 
                    {
                        reader                  =   cmd.ExecuteReader(CommandBehavior.SequentialAccess);
                        while (reader.Read())
                            sc.Add(reader.GetString(0));
                    }
                    catch
                    {
                        throw;
                    }
                    finally
                    {
                        if (reader != null)
                            reader.Close();
                    }
                    if (sc.Count < 1)
                    {
                        switch(GetReturnValue(cmd))
                        {
                        case 0:
                            return new string[0];
                        case 1:
                            throw new ProviderException(SR.GetString(SR.Provider_role_not_found, roleName));
                        }
                        throw new ProviderException(SR.GetString(SR.Provider_unknown_failure));
                    }

                    String [] strReturn         =   new String[sc.Count];
                    sc.CopyTo(strReturn, 0);
                    return strReturn;
                }
                finally
                {
                    if( holder != null )
                    {
                        holder.Close();
                        holder                  =   null;
                    }
                }
            }
            catch
            {
                throw;
            }
        }

        public override  string [] GetAllRoles()
        {
   			if ( SecurityTraceSwitch.Sw.TraceVerbose )
                Trace.WriteLine( "[V]AddonNiceSqlRoleProvider GetAllRoles BEG",SecurityTraceSwitch.Sw.Info);
            try 
            {
                SqlConnectionHolder holder          =   null;

                try 
                {
                    holder                          =   SqlConnectionHelper.GetConnection(_sqlConnectionString, true);
                    CheckSchemaVersion( holder.Connection );

                    SqlCommand      cmd             =   new SqlCommand("dbo.AddonNice_Roles_GetAllRoles", holder.Connection);
                    StringCollection       sc       =   new StringCollection();
                    SqlParameter    p               =   new SqlParameter("@ReturnValue", SqlDbType.Int);
                    SqlDataReader   reader          =   null;

                    cmd.CommandType                 =   CommandType.StoredProcedure;
                    cmd.CommandTimeout              =   CommandTimeout;

                    p.Direction                     =   ParameterDirection.ReturnValue;
                    cmd.Parameters.Add(p);
                    cmd.Parameters.Add(CreateInputParam("@ApplicationName", SqlDbType.NVarChar, ApplicationName));
                    try 
                    {
                        reader                      =   cmd.ExecuteReader(CommandBehavior.SequentialAccess);
                        while (reader.Read())
                            sc.Add(reader.GetString(0));
                    }
                    catch
                    {
                        throw;
                    }
                    finally
                    {
                        if (reader != null)
                            reader.Close();
                    }

                    String [] strReturn             =   new String [sc.Count];
                    sc.CopyTo(strReturn, 0);
                    return strReturn;
                }
                finally
                {
                    if( holder != null )
                    {
                        holder.Close();
                        holder                      =   null;
                    }
                }
            }
            catch
            {
                throw;
            }
        }

        public override string[] FindUsersInRole(string roleName, string usernameToMatch)
        {
   			if ( SecurityTraceSwitch.Sw.TraceVerbose )
                Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]AddonNiceSqlRoleProvider FindUsersInRole BEG roleName: {0}, usernameToMatch: {1}",roleName,usernameToMatch),SecurityTraceSwitch.Sw.Info);
            SecUtility.CheckParameter(ref roleName, true, true, true, 256, "roleName");
            SecUtility.CheckParameter(ref usernameToMatch, true, true, false, 256, "usernameToMatch");

            try 
            {
                SqlConnectionHolder holder  =   null;

                try 
                {
                    holder                  =   SqlConnectionHelper.GetConnection(_sqlConnectionString, true);
                    CheckSchemaVersion( holder.Connection );

                    SqlCommand cmd          =   new SqlCommand("dbo.AddonNice_UsersInRoles_FindUsersInRole", holder.Connection);
                    SqlDataReader reader    =   null;
                    SqlParameter p          =   new SqlParameter("@ReturnValue", SqlDbType.Int);
                    StringCollection sc     =   new StringCollection();

                    cmd.CommandType         =   CommandType.StoredProcedure;
                    cmd.CommandTimeout      =   CommandTimeout;

                    p.Direction             =   ParameterDirection.ReturnValue;
                    cmd.Parameters.Add(p);
                    cmd.Parameters.Add(CreateInputParam("@ApplicationName", SqlDbType.NVarChar, ApplicationName));
                    cmd.Parameters.Add(CreateInputParam("@RoleName", SqlDbType.NVarChar, roleName));
                    cmd.Parameters.Add(CreateInputParam("@UserNameToMatch", SqlDbType.NVarChar, usernameToMatch));
                    try 
                    {
                        reader              =   cmd.ExecuteReader(CommandBehavior.SequentialAccess);
                        while (reader.Read())
                            sc.Add(reader.GetString(0));
                    }
                    catch
                    {
                        throw;
                    }
                    finally
                    {
                        if (reader != null)
                            reader.Close();
                    }
                    if (sc.Count < 1)
                    {
                        switch (GetReturnValue(cmd))
                        {
                        case 0:
                            return new string[0];

                        case 1:
                            throw new ProviderException(SR.GetString(SR.Provider_role_not_found, roleName));

                        default:
                            throw new ProviderException(SR.GetString(SR.Provider_unknown_failure));
                        }
                    }
                    String[] strReturn      =   new String[sc.Count];
                    sc.CopyTo(strReturn, 0);
                    return strReturn;
                }
                finally
                {
                    if( holder != null )
                    {
                        holder.Close();
                        holder = null;
                    }
                }
            }
            catch
            {
                throw;
            }
        }

        string _DefAppName              =   string.Empty;
        public override  string ApplicationName
        {
            get 
            { 
                string _AppName         =   string.Empty;
				PortalSettings p        =   (PortalSettings)HttpContext.Current.Items[StStr.strPortalSettings];
                if ( p != null )
					 _AppName           =   p.BaseAlias;
                else _AppName           =   _DefAppName;
                return _AppName; 
            }
            set
            {
                if (String.IsNullOrEmpty(value))
                    throw new ArgumentNullException("value");

                if (value.Length > 256)
                    throw new ProviderException( SR.GetString( SR.Provider_application_name_too_long ) );
                _DefAppName             =   value;
            }
        }

		int _DefPortalID            =   0;
		public int PortalID
		{
			get 
			{
                int _PortalID       =   _DefPortalID;
				PortalSettings p    =   (PortalSettings)HttpContext.Current.Items[StStr.strPortalSettings];
                if ( p != null )
				    _PortalID       =   p.PortalID;
				return _PortalID; 
			}
            set
            {
                _DefPortalID            =  value;
            }
        }

        private SqlParameter CreateInputParam(string paramName, SqlDbType dbType, object objValue)
        {
   			if ( SecurityTraceSwitch.Sw.TraceVerbose )
                Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]AddonNiceSqlRoleProvider CreateInputParam BEG paramName: {0}, objValue: {1}",paramName,objValue),SecurityTraceSwitch.Sw.Info);
            SqlParameter param  =   new SqlParameter(paramName, dbType);
            if (objValue == null)
                objValue        =   String.Empty;
            param.Value         =   objValue;
            return param;
        }

        private int GetReturnValue(SqlCommand cmd) 
        {
            foreach(SqlParameter param in cmd.Parameters){
                if (param.Direction == ParameterDirection.ReturnValue && param.Value != null && param.Value is int)
                    return (int) param.Value;
            }
            return -1;
        }
    }
}



